﻿Imports DTOLayer
Imports System.Data.SqlClient
Public Class ThongTinKhachHangDao
    Public Sub Them(ByVal khDto As ThongTinKhachHangDto)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Insert into ThongTinKhachHang(TenKH,DiaChi,DienThoai,Email,TienNo) values(?,?,?,?,?)"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)
        cmd.Parameters.Add("@TenKH", SqlDbType.NVarChar)
        cmd.Parameters("@TenKH").Value = khDto.TenKH
        cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar)
        cmd.Parameters("@DiaChi").Value = khDto.DiaChi
        cmd.Parameters.Add("@DienThoai", SqlDbType.VarChar)
        cmd.Parameters("@DienThoai").Value = khDto.DienThoai
        cmd.Parameters.Add("@Email", SqlDbType.VarChar)
        cmd.Parameters("@Email").Value = khDto.Email
        cmd.Parameters.Add("@TienNo", SqlDbType.Float)
        cmd.Parameters("@TienNo").Value = khDto.TienNo
        cmd.ExecuteNonQuery()
        strSQL = "Select @@IDENTITY"
        cmd = New SqlCommand(strSQL, cn)
        khDto.IdKH = cmd.ExecuteScalar()
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

    Public Sub Sua(ByVal khDto As ThongTinKhachHangDto)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Update ThongTinKhachHang set TenKH=?, DiaChi=?, DienThoai=?,Email=?, TienNo=? where idKH=?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)
        cmd.Parameters.Add("@TenKH", SqlDbType.NVarChar)
        cmd.Parameters("@TenKH").Value = khDto.TenKH
        cmd.Parameters.Add("@DiaChi", SqlDbType.NVarChar)
        cmd.Parameters("@DiaChi").Value = khDto.DiaChi
        cmd.Parameters.Add("@DienThoai", SqlDbType.VarChar)
        cmd.Parameters("@DienThoai").Value = khDto.DienThoai
        cmd.Parameters.Add("@Email", SqlDbType.VarChar)
        cmd.Parameters("@Email").Value = khDto.Email
        cmd.Parameters.Add("@TienNo", SqlDbType.Float)
        cmd.Parameters("@TienNo").Value = khDto.TienNo
        cmd.Parameters.Add("@idKH", SqlDbType.Int)
        cmd.Parameters("@idKH").Value = khDto.IdKH                     
        cmd.ExecuteNonQuery()
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

    Public Sub Xoa(ByVal idKh As Integer)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "delete ThongTinKhachHang where idKH=?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)        
        cmd.Parameters.Add("@idKH", SqlDbType.Int)
        cmd.Parameters("@idKH").Value = idKh
        cmd.ExecuteNonQuery()                
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

    Public Function TraCuu(ByVal idKh As Integer) As ThongTinKhachHangDto
        Dim thongTinKH As ThongTinKhachHangDto = New ThongTinKhachHangDto()
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * from ThongTinKhachHang where idKH=?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)
        cmd.Parameters.Add("@idKH", SqlDbType.Int)
        cmd.Parameters("@idKH").Value = idKh
        cmd.ExecuteNonQuery()        
        Dim da As SqlDataReader        
        da = cmd.ExecuteReader()
        While da.Read()
            thongTinKH.IdKH = idKh
            thongTinKH.TenKH = da("TenKH")
            thongTinKH.DiaChi = da("DiaChi")
            thongTinKH.Email = da("Email")
            thongTinKH.DienThoai = da("DienThoai")
            thongTinKH.TienNo = da("TienNo")
        End While
        'B5: Dong ket noi CSDL
        cn.Close()
        Return thongTinKH
    End Function

    Public Function LayDanhSach() As List(Of ThongTinKhachHangDto)
        Dim danhSachKH As New List(Of ThongTinKhachHangDto)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * from ThongTinKhachHang"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)
        Dim da As SqlDataReader                        
        da = cmd.ExecuteReader()
        While da.Read()
            Dim thongTinKH As ThongTinKhachHangDto = New ThongTinKhachHangDto()
            thongTinKH.IdKH = da("idKH")
            thongTinKH.TenKH = da("TenKH")
            thongTinKH.DiaChi = da("DiaChi")
            thongTinKH.Email = da("Email")
            thongTinKH.DienThoai = da("DienThoai")
            thongTinKH.TienNo = da("TienNo")
            danhSachKH.Add(thongTinKH)
        End While
        'B5: Dong ket noi CSDL
        cn.Close()
        Return danhSachKH
    End Function

    Public Function LayBang() As DataTable
        Dim dt As New DataTable()
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From ThongTinKhachHang"
        'B4: Thuc thi chuoi strSQL
        Dim da As New SqlDataAdapter(strSQL, cn)
        da.Fill(dt)
        'B5: Dong ket noi CSDL
        cn.Close()
        Return dt
    End Function

    Public Sub CapNhatBang(ByVal dt As DataTable)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From ThongTinKhachHang"
        'B4: Thuc thi chuoi strSQL
        Dim da As New SqlDataAdapter(strSQL, cn)
        Dim cb As New SqlCommandBuilder(da)
        da.Update(dt)
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub
End Class
